<!DOCTYPE html>
<html lang="zh-cn">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <title>快速上手！如何使用MySQL导入CSV格式数据 - (power up)</title>
  <meta name="renderer" content="webkit" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>

<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />

<meta name="theme-color" content="#f8f5ec" />
<meta name="msapplication-navbutton-color" content="#f8f5ec">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="#f8f5ec">


<meta name="author" content="Kevin Jiang" /><meta name="description" content="在大数据时代，我们每天面临着大量的数据需要处理。表格型数据是最常见的一种，特别是财务从业者。如何快速的把CSV类型的数据导入到数据库进行快速" /><meta name="keywords" content="MySQL, MySQL快速导入CSV格式数据" />






<meta name="generator" content="Hugo 0.84.4 with theme even" />


<link rel="canonical" href="http://kevinjiang.info/post/mysql/mysql%E5%AF%BC%E5%85%A5csv%E6%95%B0%E6%8D%AE/" />
<link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
<link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png">
<link rel="manifest" href="/manifest.json">
<link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5">



<link href="/sass/main.min.78f8f17bab244b9ee62ad16480c9584d5fc2db06ae20681d1ca225cefd80767c.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.css" integrity="sha256-7TyXnr2YU040zfSP+rEcz29ggW4j56/ujTPwjMzyqFY=" crossorigin="anonymous">


<meta property="og:title" content="快速上手！如何使用MySQL导入CSV格式数据" />
<meta property="og:description" content="在大数据时代，我们每天面临着大量的数据需要处理。表格型数据是最常见的一种，特别是财务从业者。如何快速的把CSV类型的数据导入到数据库进行快速" />
<meta property="og:type" content="article" />
<meta property="og:url" content="http://kevinjiang.info/post/mysql/mysql%E5%AF%BC%E5%85%A5csv%E6%95%B0%E6%8D%AE/" /><meta property="article:section" content="post" />
<meta property="article:published_time" content="2022-12-01T15:46:07+08:00" />
<meta property="article:modified_time" content="2022-12-01T15:46:07+08:00" />

<meta itemprop="name" content="快速上手！如何使用MySQL导入CSV格式数据">
<meta itemprop="description" content="在大数据时代，我们每天面临着大量的数据需要处理。表格型数据是最常见的一种，特别是财务从业者。如何快速的把CSV类型的数据导入到数据库进行快速"><meta itemprop="datePublished" content="2022-12-01T15:46:07+08:00" />
<meta itemprop="dateModified" content="2022-12-01T15:46:07+08:00" />
<meta itemprop="wordCount" content="2125">
<meta itemprop="keywords" content="MySQL,数据库," /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="快速上手！如何使用MySQL导入CSV格式数据"/>
<meta name="twitter:description" content="在大数据时代，我们每天面临着大量的数据需要处理。表格型数据是最常见的一种，特别是财务从业者。如何快速的把CSV类型的数据导入到数据库进行快速"/>

<!--[if lte IE 9]>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
<![endif]-->

<!--[if lt IE 9]>
  <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<![endif]-->

</head>
<body>
  <div id="mobile-navbar" class="mobile-navbar">
  <div class="mobile-header-logo">
    <a href="/" class="logo">(Power up)</a>
  </div>
  <div class="mobile-navbar-icon">
    <span></span>
    <span></span>
    <span></span>
  </div>
</div>
<nav id="mobile-menu" class="mobile-menu slideout-menu">
  <ul class="mobile-menu-list">
    <a href="/">
        <li class="mobile-menu-item">首页</li>
      </a><a href="/post/">
        <li class="mobile-menu-item">归档</li>
      </a><a href="/tags/">
        <li class="mobile-menu-item">标签</li>
      </a><a href="/categories/">
        <li class="mobile-menu-item">分类</li>
      </a><a href="/resume/">
        <li class="mobile-menu-item">简历</li>
      </a>
  </ul>

  


</nav>

  <div class="container" id="mobile-panel">
    <header id="header" class="header">
        <div class="logo-wrapper">
  <a href="/" class="logo">(Power up)</a>
</div>





<nav class="site-navbar">
  <ul id="menu" class="menu">
    <li class="menu-item">
        <a class="menu-item-link" href="/">首页</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/post/">归档</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/tags/">标签</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/categories/">分类</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/resume/">简历</a>
      </li>
  </ul>
</nav>

    </header>

    <main id="main" class="main">
      <div class="content-wrapper">
        <div id="content" class="content">
          <article class="post">
    
    <header class="post-header">
      <h1 class="post-title">快速上手！如何使用MySQL导入CSV格式数据</h1>

      <div class="post-meta">
        <span class="post-time"> 2022-12-01 </span>
        <div class="post-category">
            <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
            </div>
          <span class="more-meta"> 约 2125 字 </span>
          <span class="more-meta"> 预计阅读 5 分钟 </span>
        
      </div>
    </header>

    <div class="post-toc" id="post-toc">
  <h2 class="post-toc-title">文章目录</h2>
  <div class="post-toc-content always-active">
    <nav id="TableOfContents">
  <ul>
    <li><a href="#load-data语句定义">LOAD DATA语句定义</a></li>
    <li><a href="#导入csv数据">导入CSV数据</a>
      <ul>
        <li><a href="#输入预处理">输入预处理</a></li>
        <li><a href="#转义">转义</a></li>
      </ul>
    </li>
    <li><a href="#权限">权限</a>
      <ul>
        <li><a href="#secure_file_priv-配置项"><code>secure_file_priv</code> 配置项</a></li>
      </ul>
    </li>
    <li><a href="#配置">配置</a></li>
    <li><a href="#总结">总结</a></li>
    <li><a href="#公众号">公众号</a></li>
  </ul>
</nav>
  </div>
</div>
    <div class="post-content">
      <p>在大数据时代，我们每天面临着大量的数据需要处理。表格型数据是最常见的一种，特别是财务从业者。如何快速的把CSV类型的数据导入到数据库进行快速处理呢？今天我们就来介绍其中一种。</p>
<!-- raw HTML omitted -->
<h2 id="load-data语句定义">LOAD DATA语句定义</h2>
<p>我们先来看看MySQL的 <code>LOAD DATA</code> 语句的定义</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="n">LOW_PRIORITY</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">CONCURRENT</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="k">LOCAL</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;file_name&#39;</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="k">REPLACE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">IGNORE</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">tbl_name</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="n">PARTITION</span><span class="w"> </span><span class="p">(</span><span class="n">partition_name</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="n">partition_name</span><span class="p">]</span><span class="w"> </span><span class="p">...)]</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="nb">CHARACTER</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">charset_name</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="err">{</span><span class="n">FIELDS</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">COLUMNS</span><span class="err">}</span><span class="w">
</span><span class="w">        </span><span class="p">[</span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;string&#39;</span><span class="p">]</span><span class="w">
</span><span class="w">        </span><span class="p">[[</span><span class="n">OPTIONALLY</span><span class="p">]</span><span class="w"> </span><span class="n">ENCLOSED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;char&#39;</span><span class="p">]</span><span class="w">
</span><span class="w">        </span><span class="p">[</span><span class="n">ESCAPED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;char&#39;</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="n">LINES</span><span class="w">
</span><span class="w">        </span><span class="p">[</span><span class="n">STARTING</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;string&#39;</span><span class="p">]</span><span class="w">
</span><span class="w">        </span><span class="p">[</span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;string&#39;</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="k">IGNORE</span><span class="w"> </span><span class="nb">number</span><span class="w"> </span><span class="err">{</span><span class="n">LINES</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">ROWS</span><span class="err">}</span><span class="p">]</span><span class="w">
</span><span class="w">    </span><span class="p">[(</span><span class="n">col_name_or_user_var</span><span class="w">
</span><span class="w">        </span><span class="p">[,</span><span class="w"> </span><span class="n">col_name_or_user_var</span><span class="p">]</span><span class="w"> </span><span class="p">...)]</span><span class="w">
</span><span class="w">    </span><span class="p">[</span><span class="k">SET</span><span class="w"> </span><span class="n">col_name</span><span class="o">=</span><span class="err">{</span><span class="n">expr</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">DEFAULT</span><span class="err">}</span><span class="w">
</span><span class="w">        </span><span class="p">[,</span><span class="w"> </span><span class="n">col_name</span><span class="o">=</span><span class="err">{</span><span class="n">expr</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">DEFAULT</span><span class="err">}</span><span class="p">]</span><span class="w"> </span><span class="p">...]</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p><code>LOAD DATA</code> 语句以非常高的速度将文本文件中的行读入表中。 该文件可以从服务器主机或客户端主机读取，具体取决于是否给出了 LOCAL 修饰符。 LOCAL 还会影响数据解释和错误处理。</p>
<p>LOAD DATA 是 SELECT &hellip; INTO OUTFILE 的补充。要将表中的数据写入文件，请使用 SELECT &hellip; INTO OUTFILE。 要将文件读回表中，请使用 LOAD DATA。 两个语句的 FIELDS 和 LINES 子句的语法相同。</p>
<ul>
<li>
<p><strong>LOW_PRIORITY</strong></p>
<p>使用 LOW_PRIORITY 修饰符，LOAD DATA 语句的执行被延迟，直到没有其他客户端正在从表中读取。 这只会影响仅使用表级锁定的存储引擎（例如 MyISAM、MEMORY 和 MERGE）。</p>
</li>
<li>
<p><strong>CONCURRENT</strong></p>
<p>使用 CONCURRENT 修饰符和满足并发插入条件的 MyISAM 表（即中间不包含空闲块），其他线程可以在执行 LOAD DATA 时从表中检索数据。 这个修饰符会稍微影响 LOAD DATA 的性能，即使没有其他线程同时使用该表。</p>
</li>
<li>
<p><strong>INFILE</strong></p>
<p>INFILE 后面接文件路径，可以使用绝对路径和相对路径。其中相对路径是你使用mysql登录mysql server时所在的目录</p>
</li>
<li>
<p><strong>INTO TABLE</strong></p>
<p>INTO TABLE 没什么好说的，后面指定的是表的名称</p>
</li>
<li>
<p><strong>FIELDS TERMINATED BY, LINES TERMINATED BY</strong></p>
<p>FIELDS TERMINATED BY ‘string’</p>
<p>这两个修饰符用法类型，一个是指定字段之间使用什么样的字符串 <code>string</code> 分割，一个是每一行之间使用什么样的字符串 <code>string</code> 分割。 <code>string</code> 可以超过一个字符。</p>
</li>
<li>
<p><strong>IGNORE {number} LINES</strong></p>
<p>指定忽略的行数，从第一行算。一般像csv这样的文件，第一行一般是一个表头，表示第一列的名称。{number}换成1表示忽略第一行。</p>
</li>
<li>
<p><strong>SET</strong></p>
<p>SET 子句能够在将结果分配给列之前对其值执行预处理转换。</p>
</li>
</ul>
<p>对于 <code>SET</code> 接下来在导入数据的时候还会详细介绍。</p>
<h2 id="导入csv数据">导入CSV数据</h2>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;data.csv&#39;</span><span class="w">
</span><span class="w"></span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="k">TABLE_NAME</span><span class="w">
</span><span class="w"></span><span class="n">FIELDS</span><span class="w"> </span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;,&#39;</span><span class="w">
</span><span class="w"></span><span class="n">OPTIONALLY</span><span class="w"> </span><span class="n">ENCLOSED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;&#34;&#39;</span><span class="w">
</span><span class="w"></span><span class="n">LINES</span><span class="w"> </span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;\r\n&#39;</span><span class="w">
</span><span class="w"></span><span class="k">IGNORE</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="n">LINES</span><span class="w">
</span><span class="w"></span><span class="p">(</span><span class="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">name</span><span class="p">,</span><span class="w"> </span><span class="n">value</span><span class="p">);</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>如果csv文件的每一个行的所有列都有值，则可以顺利的导入，可以看到输出内容</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback">Query OK, 407 rows affected (0.02 sec)
Records: 407  Deleted: 0  Skipped: 0  Warnings: 0
</code></pre></td></tr></table>
</div>
</div><p>但是如果csv里面的数据类型不匹配；或者某个值为空，而schema又不允许为空；又或者某一列的值你在导入的时候是不需要的。这时候需要使用到输入预处理过程了，在MySQL的 <code>LOAD DATA</code> 叫做 <strong><strong><code>Input Preprocessing</code></strong></strong></p>
<h3 id="输入预处理">输入预处理</h3>
<p>LOAD DATA 语法中的每个 col_name_or_user_var 实例都是列名或用户变量。 对于用户变量，SET 子句使您能够在将结果分配给列之前对其值执行预处理转换。</p>
<p>SET 子句中的用户变量可以多种方式使用。 下面的例子直接将第一个输入列作为t1.column1的值，将第二个输入列赋值给一个用户变量，该用户变量经过除法运算后用于t1.column2的值：</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;file.txt&#39;</span><span class="w">
</span><span class="w">  </span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t1</span><span class="w">
</span><span class="w">  </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="o">@</span><span class="n">var1</span><span class="p">)</span><span class="w">
</span><span class="w">  </span><span class="k">SET</span><span class="w"> </span><span class="n">column2</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="o">@</span><span class="n">var1</span><span class="o">/</span><span class="mi">100</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>这里的 <code>@var1</code> 保存的是每一行第二列的值，而数据库表的第二个字段为 column2，这样才可以插入到column2字段当中。</p>
<p>SET 子句可用于提供不是从输入文件派生的值。 以下语句将 column3 设置为当前日期和时间：</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;file.txt&#39;</span><span class="w">
</span><span class="w">  </span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t1</span><span class="w">
</span><span class="w">  </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">)</span><span class="w">
</span><span class="w">  </span><span class="k">SET</span><span class="w"> </span><span class="n">column3</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">CURRENT_TIMESTAMP</span><span class="p">;</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>您还可以通过将输入值分配给用户变量而不将该变量分配给任何表列来丢弃输入值：</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;file.txt&#39;</span><span class="w">
</span><span class="w">  </span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t1</span><span class="w">
</span><span class="w">  </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="o">@</span><span class="n">dummy</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">,</span><span class="w"> </span><span class="o">@</span><span class="n">dummy</span><span class="p">,</span><span class="w"> </span><span class="n">column3</span><span class="p">);</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>当mysql的表当中某个字段不允许为空，默认值为 <code>0</code> ，而csv文件当中恰好有空值的时候，可以像下面这样处理：</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;file.txt&#39;</span><span class="w">
</span><span class="w">  </span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">t1</span><span class="w">
</span><span class="w">  </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="o">@</span><span class="n">vColumn2</span><span class="p">,</span><span class="w"> </span><span class="n">column3</span><span class="p">)</span><span class="w">
</span><span class="w">  </span><span class="k">SET</span><span class="w"> </span><span class="n">column2</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">IFNULL</span><span class="p">(</span><span class="mi">0</span><span class="p">,</span><span class="w"> </span><span class="o">@</span><span class="n">vColumn2</span><span class="p">);</span><span class="w">
</span></code></pre></td></tr></table>
</div>
</div><p>这样就可以处理第二列为空的时候，默认插入值 <code>0</code> 了</p>
<p>列/变量列表和 SET 子句的使用受以下限制：</p>
<ul>
<li>SET 子句中的赋值应该只有赋值运算符左侧的列名。</li>
<li>您可以在 SET 赋值的右侧使用子查询。 返回要分配给列的值的子查询可能只是标量子查询。 此外，您不能使用子查询从正在加载的表中进行选择。</li>
<li>不会为列/变量列表或 SET 子句处理被 IGNORE number LINES 子句忽略的行。</li>
<li>加载固定行格式的数据时不能使用用户变量，因为用户变量没有显示宽度。</li>
</ul>
<h3 id="转义">转义</h3>
<p>需要被导入的数据需要特别注意转义，否则就会出现下面的错误</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback">Row 4322 doesn&#39;t contain data for all columns
</code></pre></td></tr></table>
</div>
</div><p>比如说 <code>&quot;\&quot;</code> 这样的原始字符串，在csv文件当中应该要写成 <code>&quot;\\&quot;</code> ，注意是两个反斜杠。</p>
<h2 id="权限">权限</h2>
<p>按上面的 <code>LOAD DATA</code> 语句导入数据，很可能会碰到下面的情况。</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="n">ERROR</span><span class="w"> </span><span class="mi">1290</span><span class="w"> </span><span class="p">(</span><span class="n">HY000</span><span class="p">):</span><span class="w"> </span><span class="n">The</span><span class="w"> </span><span class="n">MySQL</span><span class="w"> </span><span class="n">server</span><span class="w"> </span><span class="k">is</span><span class="w"> </span><span class="n">running</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="n">the</span><span class="w"> </span><span class="c1">--secure-file-priv option so it cannot execute this statement
</span></code></pre></td></tr></table>
</div>
</div><p>查看系统变量 <code>secure-file-priv</code></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">SHOW</span><span class="w"> </span><span class="n">VARIABLES</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s2">&#34;secure_file_priv&#34;</span><span class="p">;</span><span class="w">
</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">------------------+-------+
</span><span class="c1"></span><span class="o">|</span><span class="w"> </span><span class="n">Variable_name</span><span class="w">    </span><span class="o">|</span><span class="w"> </span><span class="n">Value</span><span class="w"> </span><span class="o">|</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">------------------+-------+
</span><span class="c1"></span><span class="o">|</span><span class="w"> </span><span class="n">secure_file_priv</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="w">  </span><span class="o">|</span><span class="w">
</span><span class="w"></span><span class="o">+</span><span class="c1">------------------+-------+
</span></code></pre></td></tr></table>
</div>
</div><p>可以看到 <code>secure_file_priv</code> 值为 <code>NULL</code></p>
<h3 id="secure_file_priv-配置项"><code>secure_file_priv</code> 配置项</h3>
<table>
<thead>
<tr>
<th>Command-Line Format</th>
<th>&ndash;secure-file-priv=dir_name</th>
</tr>
</thead>
<tbody>
<tr>
<td>System Variable</td>
<td>secure_file_priv</td>
</tr>
<tr>
<td>Scope</td>
<td>Global</td>
</tr>
<tr>
<td>Dynamic</td>
<td>No</td>
</tr>
<tr>
<td>Type</td>
<td>String</td>
</tr>
<tr>
<td>Default Value</td>
<td>platform specific</td>
</tr>
<tr>
<td>Valid Values</td>
<td>empty string</td>
</tr>
<tr>
<td>dirname</td>
<td></td>
</tr>
<tr>
<td>NULL</td>
<td></td>
</tr>
</tbody>
</table>
<p>该变量用于限制数据导入和导出操作，例如由 <code>LOAD DATA</code> 和 <code>SELECT ... INTO OUTFILE</code> 语句以及 <code>LOAD_FILE()</code> 函数执行的操作权限。 这些操作仅允许具有 <code>FILE</code> 权限的用户使用。</p>
<p>secure_file_priv 可以设置为如下值：</p>
<ul>
<li>空值，不做目录限制，即任何目录均可以；</li>
<li>指定目录，MySQL 会限制只能从该目录导入、或导出到该目录。目录必须已存在，MySQL 不会自动创建该目录；</li>
<li><code>NULL</code> ， 服务器禁用导入和导出操作。</li>
</ul>
<p>默认值是特定于平台的，取决于 <code>INSTALL_LAYOUT</code> CMake 选项的值，如下表所示。 如果您从源代码构建，要明确指定默认的 <code>secure_file_priv</code> 值，请使用 <code>INSTALL_SECURE_FILE_PRIVDIR</code> CMake 选项。</p>
<table>
<thead>
<tr>
<th>INSTALL_LAYOUT Value</th>
<th>Default secure_file_priv Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>STANDALONE, WIN</td>
<td>NULL (&gt;= MySQL 5.7.16), empty (&lt; MySQL 5.7.16)</td>
</tr>
<tr>
<td>DEB, RPM, SLES, SVR4</td>
<td>/var/lib/mysql-files</td>
</tr>
<tr>
<td>Otherwise</td>
<td>mysql-files under the CMAKE_INSTALL_PREFIX value</td>
</tr>
</tbody>
</table>
<p>要为 libmysqld 嵌入式服务器设置默认的 secure_file_priv 值，请使用 <code>INSTALL_SECURE_FILE_PRIV_EMBEDDEDDIR</code> CMake 选项。 此选项的默认值为 NULL。</p>
<p>服务器在启动时检查 <code>secure_file_priv</code> 的值，如果该值不安全，则将警告写入错误日志。 如果非 NULL 值为空，或者该值为数据目录或其子目录，或者所有用户都可以访问的目录，则认为它是不安全的。 如果 <code>secure_file_priv</code> 设置为不存在的路径，则服务器会将错误消息写入错误日志并退出。</p>
<h2 id="配置">配置</h2>
<p>如果要取消这种限制，可以修改 <code>my.cnf</code> 配置文件，在 <code>[mysqld]</code> 项加入下面的配置</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-fallback" data-lang="fallback">secure_file_priv=
</code></pre></td></tr></table>
</div>
</div><p>值留为空，表示不做任何限制。</p>
<h2 id="总结">总结</h2>
<p>本文介绍了如何使用mysql的sql语句导入csv格式的文件到某个表当中，涉及到了导入时的细节以及mysql的配置，这些都是笔者亲身踩过的坑，分享给大家避免再踩坑。</p>
<h2 id="公众号">公众号</h2>
<p>欢迎关注我的公众号，同步更新</p>
<p><img src="/img/qrcode_for_gh.jpg" alt="木木小小孩"></p>

    </div>

    <div class="post-copyright">
  <p class="copyright-item">
    <span class="item-title">文章作者</span>
    <span class="item-content">Kevin Jiang</span>
  </p>
  <p class="copyright-item">
    <span class="item-title">上次更新</span>
    <span class="item-content">
        2022-12-01
        
    </span>
  </p>
  
  <p class="copyright-item">
    <span class="item-title">许可协议</span>
    <span class="item-content"><a rel="license noopener" href="https://creativecommons.org/licenses/by-nc-nd/4.0/" target="_blank">CC BY-NC-ND 4.0</a></span>
  </p>
</div>
<div class="post-reward">
  <input type="checkbox" name="reward" id="reward" hidden />
  <label class="reward-button" for="reward">赞赏支持</label>
  <div class="qr-code">
    
    <label class="qr-code-image" for="reward">
        <img class="image" src="/img/wechat_pay_1242x1242.jpg">
        <span>微信打赏</span>
      </label>
    <label class="qr-code-image" for="reward">
        <img class="image" src="/img/alipay_600x600.jpg">
        <span>支付宝打赏</span>
      </label>
  </div>
</div><footer class="post-footer">
      <div class="post-tags">
          <a href="/tags/mysql/">MySQL</a>
          <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
          </div>
      <nav class="post-nav">
        <a class="prev" href="/post/%E6%98%AF%E4%BB%80%E4%B9%88%E9%80%A0%E5%B0%B1%E4%BA%86%E9%AB%98%E7%BA%A7%E5%B7%A5%E7%A8%8B%E5%B8%88%E7%BC%96%E5%86%99%E8%BD%AF%E4%BB%B6%E8%BF%98%E6%98%AF%E6%9E%84%E5%BB%BA%E7%B3%BB%E7%BB%9F/">
            <i class="iconfont icon-left"></i>
            <span class="prev-text nav-default">是什么造就了高级工程师？编写软件还是构建系统</span>
            <span class="prev-text nav-mobile">上一篇</span>
          </a>
        <a class="next" href="/post/cloudnative/docker%E5%88%A0%E9%99%A4%E9%95%9C%E5%83%8F%E7%9A%84%E5%8F%A6%E5%A4%96%E4%B8%80%E7%A7%8D%E5%A7%BF%E5%8A%BF/">
            <span class="next-text nav-default">Docker删除镜像的另外一种姿势</span>
            <span class="next-text nav-mobile">下一篇</span>
            <i class="iconfont icon-right"></i>
          </a>
      </nav>
    </footer>
  </article>
        </div>
        

  

  

      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="social-links">
      <a href="mailto:wenlin1988@126.com" class="iconfont icon-email" title="email"></a>
      <a href="https://www.linkedin.com/in/%E6%96%87%E6%9E%97-%E8%92%8B-0a3204126/" class="iconfont icon-linkedin" title="linkedin"></a>
      <a href="https://github.com/kevindragon" class="iconfont icon-github" title="github"></a>
  <a href="http://kevinjiang.info/index.xml" type="application/rss+xml" class="iconfont icon-rss" title="rss"></a>
</div>

<div class="copyright">
  <span class="power-by">
    由 <a class="hexo-link" href="https://gohugo.io">Hugo</a> 强力驱动
  </span>
  <span class="division">|</span>
  <span class="theme-info">
    主题 -
    <a class="theme-link" href="https://github.com/olOwOlo/hugo-theme-even">Even</a>
  </span>

  

  <span class="copyright-year">
    &copy;
    2015 -
    2023<span class="heart"><i class="iconfont icon-heart"></i></span><span>Kevin Jiang</span>
    <a href="https://beian.miit.gov.cn/" target="_blank">湘ICP备2022022745号</a>
  </span>
</div>

    </footer>

    <div class="back-to-top" id="back-to-top">
      <i class="iconfont icon-up"></i>
    </div>
  </div>
  
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.2.1/dist/jquery.min.js" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/slideout@1.0.1/dist/slideout.min.js" integrity="sha256-t+zJ/g8/KXIJMjSVQdnibt4dlaDxc9zXr/9oNPeWqdg=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.js" integrity="sha256-XVLffZaxoWfGUEbdzuLi7pwaUJv1cecsQJQqGLe7axY=" crossorigin="anonymous"></script>



<script type="text/javascript" src="/js/main.min.c99b103c33d1539acf3025e1913697534542c4a5aa5af0ccc20475ed2863603b.js"></script>
  <script type="text/javascript">
    window.MathJax = {
      tex: {
        inlineMath: [['$','$'], ['\\(','\\)']],
        tags: 'ams',
        }
    };
  </script>
  <script type="text/javascript" async src="/lib/mathjax/es5/tex-mml-chtml.js"></script>

<script id="baidu_analytics">
  var _hmt = _hmt || [];
  (function() {
    if (window.location.hostname === 'localhost') return;
    var hm = document.createElement("script"); hm.async = true;
    hm.src = "https://hm.baidu.com/hm.js?b73ff6d4afc4af9e582d8a5dc068bab9";
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(hm, s);
  })();
</script>






</body>
</html>
